﻿using Models;
using MySql.Data.MySqlClient;
using NLog;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Dapper.Contrib.Extensions;
using Dapper;
using Dapper.Contrib;

namespace DapperDemo
{
    public partial class Form1 : Form
    {
        #region 变量
        private Logger _log = NLog.LogManager.GetCurrentClassLogger();
        private string _connectionString = "server=localhost;database=dapper_test;user id=root;password=123456;character set=utf8mb4; min pool size=10; max pool size=50;";
        private int _count = 10000;
        #endregion

        #region Form1
        public Form1()
        {
            InitializeComponent();
        }
        #endregion

        #region Form1_Load
        private void Form1_Load(object sender, EventArgs e)
        {
            //预热
            using (IDbConnection conn = new MySqlConnection(_connectionString))
            {
                List<SysUser> list = conn.Query<SysUser>("select * from sys_user t where t.id<20").ToList();
            }
        }
        #endregion

        #region Log
        private void Log(string log)
        {
            if (!this.IsDisposed)
            {
                string msg = DateTime.Now.ToString("mm:ss.fff") + " " + log + "\r\n\r\n";

                if (this.InvokeRequired)
                {
                    this.BeginInvoke(new Action(() =>
                    {
                        textBox1.AppendText(msg);
                    }));
                }
                else
                {
                    textBox1.AppendText(msg);
                }
            }
        }
        #endregion

        #region 清空输出框
        private void button10_Click(object sender, EventArgs e)
        {
            textBox1.Text = string.Empty;
        }
        #endregion

        #region RunTask
        private void RunTask(Action action)
        {
            Task.Run(() =>
            {
                try
                {
                    action();
                }
                catch (Exception ex)
                {
                    Log(ex.Message + "\r\n" + ex.StackTrace);
                }
            });
        }
        #endregion

        #region 生成实体类
        private void button1_Click(object sender, EventArgs e)
        {
            RunTask(() =>
            {
                Log("开始生成实体类");

                Log("生成实体类完成");
            });
        }
        #endregion

        #region 测试查询
        private void button2_Click(object sender, EventArgs e)
        {
            Log("开始查询");
            List<SysUser> list = new List<SysUser>();

            using (IDbConnection conn = new MySqlConnection(_connectionString))
            {
                list = conn.Query<SysUser>("select * from sys_user t where t.id<20").ToList();
            }

            foreach (SysUser user in list)
            {
                Log(string.Format("{0}，{1}，{2}，{3}", user.user_name, user.real_name, user.remark, user.create_time.ToString("yyyy-MM-dd")));
            }
            Log("查询结束 count=" + list.Count);
        }
        #endregion

        #region 测试批量修改
        private void button3_Click(object sender, EventArgs e)
        {
            RunTask(() =>
            {
                Log("未实现");
            });
        }
        #endregion

        #region 测试批量添加
        private void button4_Click(object sender, EventArgs e)
        {
            RunTask(() =>
            {
                Log("未实现");
            });
        }
        #endregion

        #region 删除
        private void button5_Click(object sender, EventArgs e)
        {
            RunTask(() =>
            {
                Log("删除 开始");
                using (IDbConnection conn = new MySqlConnection(_connectionString))
                {
                    conn.Execute("delete from sys_user where id>20");
                }
                Log("删除 完成");
            });
        }
        #endregion

        #region 测试循环修改
        private void button7_Click(object sender, EventArgs e)
        {
            RunTask(() =>
            {
                using (IDbConnection conn = new MySqlConnection(_connectionString))
                {
                    Random rnd = new Random();
                    List<SysUser> userList = conn.Query<SysUser>("select * from sys_user t where t.id > 20").ToList();
                    Log("循环修改 开始 count=" + userList.Count.ToString("0 000"));
                    foreach (SysUser user in userList)
                    {
                        user.remark = "测试修改用户" + rnd.Next(0, 10000);
                        user.update_userid = "1";
                        user.update_time = DateTime.Now;
                    }

                    DateTime dt = DateTime.Now;
                    IDbTransaction tran = null;
                    try
                    {
                        conn.Open();
                        tran = conn.BeginTransaction();
                        foreach (SysUser user in userList)
                        {
                            conn.Update<SysUser>(user);
                        }
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran?.Rollback();
                        throw ex;
                    }
                    string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
                    Log("循环修改 完成，耗时：" + time + "秒");
                }
            });
        }
        #endregion

        #region 测试循环添加
        private void button6_Click(object sender, EventArgs e)
        {
            RunTask(() =>
            {
                using (IDbConnection conn = new MySqlConnection(_connectionString))
                {
                    List<SysUser> userList = new List<SysUser>();
                    for (int i = 1; i <= _count; i++)
                    {
                        SysUser user = new SysUser();
                        user.user_name = "testUser";
                        user.real_name = "测试插入用户";
                        user.password = "123456";
                        user.create_userid = "1";
                        userList.Add(user);
                    }

                    Log("循环添加 开始 count=" + userList.Count);
                    DateTime dt = DateTime.Now;
                    IDbTransaction tran = null;
                    try
                    {
                        conn.Open();
                        tran = conn.BeginTransaction();
                        foreach (SysUser user in userList)
                        {
                            conn.Insert<SysUser>(user);
                        }
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran?.Rollback();
                        throw ex;
                    }
                    string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
                    Log("循环添加 完成，耗时：" + time + "秒");
                }
            });
        }
        #endregion

        #region 查询
        private void button9_Click(object sender, EventArgs e)
        {
            RunTask(() =>
            {
                Log("查询 开始");
                DateTime dt = DateTime.Now;

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("id", 20);
                parameters.Add("realName", "测试");

                for (int i = 0; i < 10; i++)
                {
                    using (IDbConnection conn = new MySqlConnection(_connectionString))
                    {
                        List<SysUser> userList = conn.Query<SysUser>("select * from sys_user t where t.id>@id and t.real_name like concat('%',@realName,'%') order by t.create_time desc, t.id asc", parameters).ToList();
                        Log("查询结果 count=" + userList.Count.ToString());
                    }
                }

                string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
                Log("查询 完成，耗时：" + time + "秒");
            });
        }
        #endregion

        #region 分页查询
        private void button8_Click(object sender, EventArgs e)
        {
            RunTask(() =>
            {
                Log("未实现");
            });
        }
        #endregion

    }
}
